import csv
import time
import pymysql
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import NoSuchElementException, TimeoutException
from selenium.webdriver.edge.service import Service as EdgeService

def setup_driver():
    """初始化浏览器驱动"""
    service = EdgeService(executable_path=r'C:\Users\86178\Desktop\industry\edgedriver_win64\msedgedriver.exe')
    options = webdriver.EdgeOptions()
    # 启用无头模式
    options.add_argument('--headless')
    options.add_argument('--disable-gpu')
    options.add_argument('--window-size=1920,1080')
    options.add_argument('--disable-extensions')
    options.add_argument('--disable-dev-shm-usage')
    options.add_argument('--no-sandbox')
    driver = webdriver.Edge(service=service, options=options)
    return driver


def scroll_to_load(driver, target_count=50000, scroll_pause_time=2, max_scroll=30):
    """滚动页面直到加载足够多的书籍或达到最大滚动次数"""
    scroll_count = 0
    last_count = 0
    stagnation_count = 0

    while scroll_count < max_scroll:
        # 获取当前书籍数量
        books = driver.find_elements(By.CSS_SELECTOR, "#book_list > a")
        current_count = len(books)

        # 如果已经达到目标数量，停止滚动
        if current_count >= target_count:
            print(f"已达到目标数量，当前书籍数量: {current_count}")
            break

        # 如果数量没有增加，增加停滞计数器
        if current_count == last_count:
            stagnation_count += 1
            if stagnation_count >= 3:  # 连续3次滚动没有新内容加载
                print("连续3次滚动没有新内容加载，停止滚动")
                break
        else:
            stagnation_count = 0

        last_count = current_count

        # 执行滚动
        driver.execute_script("window.scrollTo(0, document.body.scrollHeight);")
        print(f"滚动 {scroll_count + 1}/{max_scroll}, 当前书籍数量: {current_count}")
        time.sleep(scroll_pause_time)
        scroll_count += 1

        # 随机等待，模拟人类行为
        if scroll_count % 5 == 0:
            time.sleep(1)

    # 确保返回书籍列表
    return driver.find_elements(By.CSS_SELECTOR, "#book_list > a")


def get_external_book_data(book_element):
    """从列表页获取书籍外部数据"""
    try:
        # 获取图片URL
        img_elements = book_element.find_elements(By.CSS_SELECTOR, "span.bookcover img")
        img_url = img_elements[1].get_attribute("src") if len(img_elements) > 1 else img_elements[0].get_attribute(
            "src")

        # 获取价格
        try:
            price = book_element.find_element(By.CSS_SELECTOR, ".price .now").text.strip()
        except NoSuchElementException:
            price = "N/A"

        return {
            "书名": book_element.get_attribute("title"),
            "作者": book_element.get_attribute("dd_name"),
            "价格": price,
            "图片URL": img_url,
            "详情页链接": book_element.get_attribute("href")
        }
    except Exception as e:
        print(f"获取外部数据出错: {str(e)}")
        return None


def get_internal_book_data(driver, book_url):
    """从详情页获取书籍内部数据"""
    internal_data = {
        "评分": "N/A",
        "出版社": "N/A",
        "出版日期": "N/A",
        "评论数": "0",
        "一级分类": "N/A",
        "二级分类": "N/A",
        "三级分类": "N/A"
    }

    if not book_url:
        return internal_data

    try:
        original_window = driver.current_window_handle
        driver.execute_script("window.open('');")
        driver.switch_to.window(driver.window_handles[1])
        driver.get(book_url)

        # 增加等待时间和重试机制
        try:
            WebDriverWait(driver, 10).until(
                EC.presence_of_element_located((By.CSS_SELECTOR, ".newbook_center"))
            )
        except TimeoutException:
            print(f"详情页加载超时: {book_url}")
            return internal_data

        time.sleep(1)  # 确保页面完全加载

        # 获取评分
        try:
            rating_element = driver.find_element(By.CSS_SELECTOR, ".readIndex")
            internal_data["评分"] = rating_element.text.strip()
        except NoSuchElementException:
            pass

        # 获取出版社
        try:
            publisher_element = driver.find_element(By.CSS_SELECTOR, "#publisher span a")
            internal_data["出版社"] = publisher_element.text.strip()
        except NoSuchElementException:
            pass

        # 获取出版日期
        try:
            for p in driver.find_elements(By.CSS_SELECTOR, ".explain_box p"):
                if "出版时间" in p.text:
                    internal_data["出版日期"] = p.text.replace("出版时间：", "").strip()
                    break
        except NoSuchElementException:
            pass

        # 获取评论数
        try:
            comment_element = driver.find_element(By.CSS_SELECTOR, ".count_per em")
            comment_text = comment_element.text.strip()
            if "人评论" in comment_text:
                internal_data["评论数"] = comment_text.replace("人评论", "").strip()
            elif "评" == comment_text:
                internal_data["评论数"] = "0"
        except NoSuchElementException:
            pass

        # 获取分类信息
        try:
            category_elements = driver.find_elements(By.CSS_SELECTOR, ".explain_box p:last-child span")
            categories = [element.text.strip() for element in category_elements if element.text.strip() != ">"]
            if len(categories) >= 1:
                internal_data["一级分类"] = categories[0]
            if len(categories) >= 2:
                internal_data["二级分类"] = categories[1]
            if len(categories) >= 3:
                internal_data["三级分类"] = categories[2]
        except NoSuchElementException:
            pass

    except Exception as e:
        print(f"获取内部数据出错: {str(e)}")
    finally:
        if len(driver.window_handles) > 1:
            driver.close()
            driver.switch_to.window(original_window)
        time.sleep(0.5)

    return internal_data

def save_to_csv(data, filename):
    """将数据保存到CSV文件"""
    if not data:
        print("没有数据可保存")
        return

    fieldnames = [
        "书名", "作者", "价格", "图片URL",
        "评分", "出版社", "出版日期", "评论数",
        "一级分类", "二级分类", "三级分类"
    ]

    with open(filename, 'w', newline='', encoding='utf-8-sig') as csvfile:
        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
        writer.writeheader()
        for row in data:
            filtered_row = {k: v for k, v in row.items() if k in fieldnames}
            writer.writerow(filtered_row)

    print(f"数据已保存到 {filename}")


def setup_database_connection():
    """设置数据库连接"""
    try:
        connection = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            database='books',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        print("成功连接到books数据库")
        return connection
    except Exception as e:
        print(f"数据库连接失败: {str(e)}")
        return None


def check_and_prepare_table(connection):
    """检查并准备表结构（兼容已有表）"""
    try:
        with connection.cursor() as cursor:
            # 检查表是否存在
            cursor.execute("SHOW TABLES LIKE 'books'")
            result = cursor.fetchone()

            if not result:
                # 创建新表
                sql = """
                CREATE TABLE books (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    title VARCHAR(255) NOT NULL,
                    author VARCHAR(100),
                    price DECIMAL(10,2),
                    image_url VARCHAR(512),
                    rating FLOAT,
                    review_count INT,
                    publisher VARCHAR(100),
                    publish_date VARCHAR(50),
                    category_level1 VARCHAR(50),
                    category_level2 VARCHAR(50),
                    category_level3 VARCHAR(50),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
                """
                cursor.execute(sql)
                print("已创建books表")
            else:
                print("books表已存在，跳过创建")

            connection.commit()
            return True

    except Exception as e:
        print(f"准备表结构失败: {str(e)}")
        connection.rollback()
        return False


def clean_book_data(book_data):
    """数据清洗函数"""

    # 1. 清洗书名
    def clean_title(title):
        if not title or str(title).strip() == "":
            return "未知书名"
        title = re.sub(r'【.*?】|\[.*?\]|["\']', '', str(title))
        return title.strip()

    # 2. 清洗作者
    def clean_author(author):
        if not author:
            return None
        author = re.sub(r'著|译|编|翻译|主编|,|，|;|；|、', '', str(author))
        author = re.sub(r'[·•]', '·', author)
        author = re.sub(r'\s+', ' ', author).strip()
        return author if author else None

    # 3. 清洗价格
    def clean_price(price):
        if not price or price == 'N/A':
            return 0.0
        price_str = re.sub(r'[^\d.]', '', str(price))
        try:
            return float(price_str)
        except:
            return 0.0

    # 4. 清洗出版社
    def clean_publisher(publisher):
        if not publisher or publisher == 'N/A':
            return '未知出版社'
        publisher = re.sub(r'有限公司$', '', publisher)
        publisher = re.sub(r'出版社有限公司$', '出版社', publisher)
        return publisher.strip()

    # 应用清洗
    book_data['书名'] = clean_title(book_data.get('书名'))
    book_data['作者'] = clean_author(book_data.get('作者'))
    book_data['价格'] = clean_price(book_data.get('价格'))
    book_data['出版社'] = clean_publisher(book_data.get('出版社'))
    book_data['评分'] = float(book_data['评分']) if book_data.get('评分') and book_data['评分'] != 'N/A' else 0.0
    book_data['评论数'] = int(book_data['评论数']) if book_data.get('评论数') else 0

    return book_data


def insert_or_update_book_data(connection, book_data):
    """插入或更新书籍数据"""
    try:
        cleaned_data = clean_book_data(book_data)

        with connection.cursor() as cursor:
            # 检查是否已存在
            cursor.execute(
                "SELECT id FROM books WHERE title = %s AND author = %s LIMIT 1",
                (cleaned_data['书名'], cleaned_data['作者'])
            )
            existing = cursor.fetchone()

            if existing:
                # 更新现有记录
                sql = """
                UPDATE books SET
                    price = %s, image_url = %s, rating = %s,
                    publisher = %s, publish_date = %s, review_count = %s,
                    category_level1 = %s, category_level2 = %s, category_level3 = %s,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = %s
                """
                cursor.execute(sql, (
                    cleaned_data['价格'],
                    cleaned_data['图片URL'],
                    cleaned_data['评分'],
                    cleaned_data['出版社'],
                    cleaned_data['出版日期'],
                    cleaned_data['评论数'],
                    cleaned_data['一级分类'],
                    cleaned_data['二级分类'],
                    cleaned_data['三级分类'],
                    existing['id']
                ))
                print(f"更新书籍: {cleaned_data['书名']}")
            else:
                # 插入新记录
                sql = """
                INSERT INTO books (
                    title, author, price, image_url, rating, publisher,
                    publish_date, review_count, category_level1,
                    category_level2, category_level3
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                cursor.execute(sql, (
                    cleaned_data['书名'],
                    cleaned_data['作者'],
                    cleaned_data['价格'],
                    cleaned_data['图片URL'],
                    cleaned_data['评分'],
                    cleaned_data['出版社'],
                    cleaned_data['出版日期'],
                    cleaned_data['评论数'],
                    cleaned_data['一级分类'],
                    cleaned_data['二级分类'],
                    cleaned_data['三级分类']
                ))
                print(f"新增书籍: {cleaned_data['书名']}")

        connection.commit()
        return True
    except Exception as e:
        print(f"数据库操作失败: {str(e)}")
        connection.rollback()
        return False


def main():
    """主函数"""
    # 初始化数据库连接
    db_connection = setup_database_connection()
    if db_connection and not check_and_prepare_table(db_connection):
        print("警告: 数据库表准备失败，将继续运行但不保存到数据库")
        db_connection.close()
        db_connection = None

    driver = setup_driver()
    book_data_list = []
    target_count = 3  # 目标数量

    try:
        print("正在访问当当网图书页面...")
        url = "https://e.dangdang.com/classification_list_page.html?category=SK&dimension=dd_sale&order=0"
        driver.get(url)

        WebDriverWait(driver, 15).until(
            EC.presence_of_element_located((By.CSS_SELECTOR, "#book_list > a")))

        print(f"开始滚动加载，目标数量: {target_count}本...")
        books = scroll_to_load(driver, target_count)
        print(f"共获取到 {len(books)} 本书籍")

        print("开始提取书籍数据...")
        for i, book in enumerate(books[:target_count], 1):
            print(f"处理进度: {i}/{min(len(books), target_count)}")

            external_data = get_external_book_data(book)
            if not external_data:
                continue

            internal_data = get_internal_book_data(driver, external_data["详情页链接"])
            book_data = {**external_data, **internal_data}
            book_data_list.append(book_data)

            # 保存到数据库
            if db_connection:
                insert_or_update_book_data(db_connection, book_data)

        # 保存到CSV
        save_to_csv(book_data_list, "dangdang_books.csv")
        print(f"\n完成! 共提取 {len(book_data_list)} 本有效书籍数据")

    except Exception as e:
        print(f"程序运行出错: {str(e)}")
    finally:
        driver.quit()
        if db_connection:
            db_connection.close()
        print("资源已释放")


if __name__ == "__main__":
    import re  # 添加到文件顶部
    main()